﻿using System;
using System.Collections.Generic;
using System.Text;

using HZ.Common;
using System.Data;
using System.Data.SqlClient;
using HZ.Supervise.Model;

namespace HZ.Supervise.DAL
{
	public class DAL_Goods
	{
		public static DataTable GetGoodsBySortID(string productName, string _sortId, string _SortSource, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "";
			string s1 = "", s2 = "";
			if (_sortId != "---请选择---")
			{
				s1 = "SortID =" + _sortId;
			}
			else
			{
				s1 = "1=1";
			}
			if (_SortSource != "")
			{
				s2 = "Source=" + _SortSource;
			}
			else
			{
				s2 = "Source in (0,1,2)";
			}
			if (!productName.Equals(""))
			{
				sql = string.Format("select SortID, TB_ProcureCatalog.GoodsID,ProductName FROM TB_Goods ,TB_ProcureCatalog where IsUsing=1 and TB_ProcureCatalog.GoodsID=TB_Goods.GoodsID  and 1=1 and {0} and {1} and productName like'%{2}%' ORDER BY SortID", s1, s2, productName);
			}
			else
			{
				sql = string.Format("select SortID, TB_ProcureCatalog.GoodsID,ProductName FROM TB_Goods ,TB_ProcureCatalog where IsUsing=1 and TB_ProcureCatalog.GoodsID=TB_Goods.GoodsID   and 1=1 and {0} and {1} ORDER BY SortID", s1, s2);
			}

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;

			return des.GetSpecailPage();
		}
		/// <summary>
		/// 根据hospitalid查询药品采购目录
		/// </summary>
		/// <param name="hospitalId"></param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GetGoodsByHospitalId(string productName, string _sortId, string _SortSource, string hospitalId, int currentpage, int pagesize, out int totalrecord)
		{
			string s1 = "", s2 = "";
			if (_sortId != "---请选择---")
			{
				s1 = "SortID =" + _sortId;
			}
			else
			{
				s1 = "1=1";
			}
			if (_SortSource != "")
			{
				s2 = "Source=" + _SortSource;
			}
			else
			{
				s2 = "1=1";
			}
			string sql = "";
			if (!productName.Equals(""))
			{
				sql = string.Format("select * from View_ProcureCatalog where GoodsID not in(select HGoodsId from TB_HospitalGoods where HHospitalID='{0}')and  {1} and {2} and IsUsing=1 and ProductName like'%{3}%' order by goodsId", hospitalId, s1, s2, productName);
			}
			else
			{
				sql = string.Format("select * from View_ProcureCatalog where GoodsID not in(select HGoodsId from TB_HospitalGoods where HHospitalID='{0}')and  {1} and {2} and IsUsing=1  order by goodsId", hospitalId, s1, s2);
			}

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;

			return des.GetSpecailPage();
		}
		/// <summary>
		/// 返回月排名
		/// </summary>
		/// <param name="_strmonth1">选择月份</param>
		/// <param name="_strmonth2">上月份</param>
		/// <param name="_strorderby">排序字段</param>
		///<param name="strwhere">查询条件</param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GeteGoodsRanking(string Sort, string strwhere, string strwherelast, string Order,string wherePro, int currentpage, int pagesize, out int totalrecord)
		    {

			string sql = string.Empty;

            sql = string.Format(@"SELECT A.apm,ISNULL(b.pm,0) as pm, ISNULL(b.pm-a.apm,a.apm) as pmc,A.ProductName,A.CompanyName_Sc,A.MedicineModel,A.Outlookc,A.PackUnit,A.Unit,A.Pack,A.DistributeAmountTotal,A.DistributeCountTotal,A.PurchaseAmountTotal,A.PURCHASECOUNTTotal,A.WarehouseAmountTotal,A.WarehouseCountTotal,a.DISTRIBUTERATE,A.warehouserate,A.Factor  FROM (	
                   SELECT RANK() OVER (ORDER BY {0} DESC) apm,ProductName,MedicineModel,Outlookc,PackUnit,Unit,Pack,Factor,CompanyName_Sc,SUM(PURCHASECOUNT) as PurchaseCountTotal,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal , SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount)  as WarehouseCountTotal,Case when sum(PurchaseAmount)=0 then 0 when sum(DistributeAmount)=0 then 0 else CONVERT(float, SUM(DistributeAmount)) / SUM(isnull(PurchaseAmount,0)) end as DistributeRate,Case when sum(PurchaseAmount)=0 then 0 when sum(WarehouseAmount)=0 then 0 else CONVERT(float, SUM(WarehouseAmount)) / SUM(isnull(PurchaseAmount,0)) end  as warehouserate 
FROM dbo.CACHE_STAT_Goods where 1=1 and DayTime={1} GROUP BY 	ProductName, MedicineModel,Outlookc,Pack,Unit,PackUnit,Factor,CompanyName_Sc) AS A LEFT JOIN (SELECT RANK() OVER (ORDER BY {0} DESC) PM,ProductName,MedicineModel,Outlookc,PackUnit,Factor,Pack,Unit,CompanyName_Sc,SUM(PurchaseCount) as PurchaseCountTotal,SUM(PurchaseAmount) as PurchaseAmountTotal ,SUM(DistributeAmount) as DistributeAmountTotal,SUM(DistributeCount) as DistributeCountTotal,case when avg(ISNULL(DistributeRate,0))>1 then 1 else avg(ISNULL(DistributeRate,0)) end    DistributeRate,SUM(WarehouseAmount) as WarehouseAmountTotal,SUM(WarehouseCount) as WarehouseCountTotal,case when AVG(ISNULL(WareHouseRate,0)) > 1 then 1 else avg(ISNULL(WareHouseRate,0)) end WareHouseRate  FROM dbo.CACHE_STAT_Goods where 1=1  and DayTime={3} GROUP BY 	ProductName, MedicineModel,Outlookc,PackUnit,Pack,Unit,Factor,CompanyName_Sc) AS B ON A.ProductName=B.ProductName AND A.CompanyName_Sc=B.CompanyName_Sc AND A.MedicineModel=B.MedicineModel AND A.Outlookc=B.Outlookc AND A.Factor=B.Factor AND A.PackUnit=B.PackUnit and A.Pack=B.Pack AND A.Unit=B.Unit where 1=1 {4}  ORDER BY {2}", Sort, strwhere, Order, strwherelast, wherePro);

			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;


			return des.GetSpecailPage();
		}

		/// <summary>
		/// 返回月排名(按时间段)
		/// </summary>
		/// <param name="_strmonth1">选择月份</param>
		/// <param name="_strmonth2">上月份</param>
		/// <param name="_strorderby">排序字段</param>
		///<param name="strwhere">查询条件</param>
		/// <param name="currentpage"></param>
		/// <param name="pagesize"></param>
		/// <param name="totalrecord"></param>
		/// <returns></returns>
		public static DataTable GeteGoodsRankingTime(string _strmonth1, string _strorderby, string strwhere, int currentpage, int pagesize, out int totalrecord)
		{
			string sql = "select * from ( select a.pm AS apm , a.ProductName , a.MedicineModelName , a.Outlookc , a.Factor , a.Unit , a.CompanyName_SC , a.SortName , a.GoodsName , a.Pack , a.QualityLevel ,a.SortSource ,a.ProcureCatalogID , a.PurchaseCountTotal ,a.PurchaseAmountTotal , a.DistributeRate ,a.DistributeCountTotal ,a.WareHouseCountTotal , a.WareHouseRate , a.WareHouseAmountTotal , a.DistributeAmountTotal from (SELECT RANK() over( order by " + _strorderby + " ) pm ,ProductName,MedicineModelName,Outlookc,Factor,Unit,CompanyName_SC,SortName,CompanyName_TB,GoodsName,Pack,QualityLevel,ProcurePrice SortSource,B.ProcureCatalogID,PurchaseCountTotal, PurchaseAmountTotal, DistributeRate, DistributeCountTotal, WareHouseCountTotal, WareHouseRate, WareHouseAmountTotal, DistributeAmountTotal FROM View_ProcureCatalog INNER JOIN (SELECT ProcureCatalogID,Case  WHEN SUM(DistributeCount)=0 THEN NULL WHEN SUM(PurchaseCount)=0 THEN NULL  ELSE  CONVERT(FLOAT,SUM(DistributeCount*Price)) / SUM(PurchaseAmount) END   AS DistributeRate,SUM(PurchaseAmount) AS PurchaseAmountTotal,SUM(PurchaseCount) AS PurchaseCountTotal,SUM(DistributeCount) AS DistributeCountTotal,SUM(WarehouseCount) AS WareHouseCountTotal,Case when sum(WareHouseCount)=0 then null when sum(PurchaseCount)=0 then NULL else CONVERT(FLOAT,SUM(WareHouseAmount)) / SUM(PurchaseAmount) end  AS WareHouseRate,SUM(WarehouseAmount) AS WareHouseAmountTotal,SUM(DistributeCount*Price) AS DistributeAmountTotal FROM View_Supervise WHERE 1=1 AND (" + _strmonth1 + ") GROUP BY ProcureCatalogID) AS B ON View_ProcureCatalog.ProcureCatalogID = B.ProcureCatalogID " + strwhere + ") a) as n order by apm";
			HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);

			des.CurrentPage = currentpage;
			totalrecord = des.TotalCount;

			return des.GetSpecailPage();
		}
	}
}
